package edzest.basic;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class J23_MemberDAO implements J23_MemberService {
	
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";
	
	private static final String insertSQL = "";
	private static final String updateSQL = "";
	private static final String deleteSQL = "";
	private static final String selectSQL = 
			" select userid, jumin, mp from member order by userid ";
	private static final String selectOneSQL = 
			" select * from member where userid = ? ";

	@Override
	public int insertMember(J23_Member m) {
		int cnt = 0;
		return cnt;
	}

	@Override
	public int updateMember(J23_Member m) {
		int cnt = 0;
		return cnt;
	}

	@Override
	public int deleteMember(String m) {
		int cnt = 0;
		return cnt;
	}

	@Override
	public ArrayList<J23_Member> readMember() {
		
		ArrayList<J23_Member> data = new ArrayList<>();
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL); // SQL문 만들기
			rs = ps.executeQuery(); // SQL문 실행하고 결과값 저장
			
			while( rs.next() ){
				J23_Member m = new J23_Member(
						rs.getString(1), rs.getString(2),
						null, null, null, null,
						rs.getString(3), null);
						// userid, jumin, 
						// pwd, zip, addr, tel, 
						// mp, pay)
						// 서버에 부하걸릴까봐 중요한 컬럼인 userid, jumin, mp만 가져옴
						
						// 결과집합 rs에 저장된 모든 데이터 중
						// 레코드 단위(행)로 하나씩 읽어와서
						// J23_Member 클래스에 저장하고
						// ArrayList인 data변수에 추가함
				data.add( m ); 
				// 가져온 레코드를 ArrayList(동적배열) data에 저장
				// 이 메소드가 data를 return하므로 data.add( m );안하면 결과 안뜸
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(rs != null)
				try{rs.close();}catch(Exception e){} // try쓴 이유: rs.close가 제대로 안되더라도 밑의 코드들을 실행하기 위해
			if(ps != null)
				try{ps.close();}catch(Exception e){}
			if(conn != null)
				try{conn.close();}catch(Exception e){}
		}
		
		return data;
	}

	@Override
	public J23_Member readOneMember(String m) {
		J23_Member data = null;
		return data;
	}

}
